﻿using Dapper;
using StudentManager.Data.IDAL;
using StudentManager.Data.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace StudentManager.Data.DAL
{
    public class ZbUserInfoDAL : IZbtUserInfoDAL
    {
        private readonly IConnectionFactory _connectionFactory;

        public ZbUserInfoDAL(IConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }
          // 根据ID查询
        public async Task<ZbUserInfo> GetByIdAsync(int id)
        {
            using var connection = _connectionFactory.GetConnection();
            var sql = "SELECT * FROM ZbUserInfo WHERE ZbUserID = @Id";
            return await connection.QueryFirstOrDefaultAsync<ZbUserInfo>(sql, new { Id = id });
        }

        public async Task<int> AddAsync(ZbUserInfo user)
        {
            using var connection = _connectionFactory.GetConnection();
            var sql = @"INSERT INTO ZbUserInfo 
                       (ZbUserName, ZbPassword, ZbEmail, ZbPhone, ZbAddress, ZbRegDateTime) 
                       VALUES (@ZbUserName, @ZbPassword, @ZbEmail, @ZbPhone, @ZbAddress, GETDATE());
                       SELECT CAST(SCOPE_IDENTITY() as int)";

            return await connection.ExecuteScalarAsync<int>(sql, user);
        }

        public async Task<bool> UpdateAsync(ZbUserInfo user)
        {
            using var connection = _connectionFactory.GetConnection();
            var sql = @"UPDATE ZbUserInfo SET 
                       ZbUserName = @ZbUserName, 
                       ZbPassword = @ZbPassword, 
                       ZbEmail = @ZbEmail, 
                       ZbPhone = @ZbPhone, 
                       ZbAddress = @ZbAddress 
                       WHERE ZbUserID = @ZbUserID";

            var affectedRows = await connection.ExecuteAsync(sql, user);
            return affectedRows > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = _connectionFactory.GetConnection();
            var sql = "DELETE FROM ZbUserInfo WHERE ZbUserID = @Id";
            var affectedRows = await connection.ExecuteAsync(sql, new { Id = id });
            return affectedRows > 0;
        }

        public async Task<(IEnumerable<ZbUserInfo> Users, int TotalCount)> GetPagedListAsync(int pageIndex, int pageSize, string searchKey = "")
        {
            using var connection = _connectionFactory.GetConnection();

            var whereClause = string.IsNullOrEmpty(searchKey) ? "" :
                "WHERE ZbUserName LIKE @SearchKey OR ZbEmail LIKE @SearchKey OR ZbPhone LIKE @SearchKey";

            var searchParam = string.IsNullOrEmpty(searchKey) ? null : $"%{searchKey}%";

            var countSql = $"SELECT COUNT(*) FROM ZbUserInfo {whereClause}";
            var totalCount = await connection.ExecuteScalarAsync<int>(countSql, new { SearchKey = searchParam });

            var dataSql = $@"
                SELECT * FROM ZbUserInfo 
                {whereClause}
                ORDER BY ZbUserID 
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            var users = await connection.QueryAsync<ZbUserInfo>(dataSql, new
            {
                SearchKey = searchParam,
                Offset = (pageIndex - 1) * pageSize,
                PageSize = pageSize
            });

            return (users, totalCount);
        }
    }
}
